package org.brisling.common.util;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.brisling.base.domain.ExportExcelDomain;

/**
 * 导出Excel公共方法
 * @version 1.0
 * 
 * @author wangcp
 *
 */
@SuppressWarnings("deprecation")
public class ExportUtil<T> {
	
	//显示的导出表的标题
	private String title;
	
	//显示的导出表的页脚
	private String foot = null;
	//导出表的列名
	private String[] rowName ;
	
	private List<ExportExcelDomain> rowConfig ;
	
	private List<Object[]>  dataList = new ArrayList<Object[]>();
	
	private List<T> domainList = new ArrayList<T>();
	
	
	
	
	//构造方法，传入要导出的数据
	public ExportUtil(String title,String[] rowName,List<Object[]>  dataList){
		this.dataList = dataList;
		this.rowName = rowName;
		this.title = title;
	}
	
	public ExportUtil(String title,List<ExportExcelDomain> rowConfig,List<T> datalist){
		this.title = title;		
		this.rowConfig = rowConfig;
		this.domainList = datalist;
	}
	
	public ExportUtil(String title,String foot,List<ExportExcelDomain> rowConfig,List<T> datalist){
		this.title = title;
		this.foot = foot;
		this.rowConfig = rowConfig;
		this.domainList = datalist;
	}
	
	/*
	 * 导出数据
	 * */
	public void exportByDomain(HttpServletResponse response) throws Exception{
		try{
			HSSFWorkbook workbook = new HSSFWorkbook();						// 创建工作簿对象
			HSSFSheet sheet = workbook.createSheet(title);		 			// 创建工作表
			
			// 产生表格标题行
	        HSSFRow rowm = sheet.createRow(0);
	        HSSFCell cellTiltle = rowm.createCell(0);
	        
	        //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
	        HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
	        HSSFCellStyle style = this.getStyle(workbook);					//单元格样式对象
	        
	        
	        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowConfig.size()-1)));  
	        cellTiltle.setCellStyle(columnTopStyle);
	        cellTiltle.setCellValue(title);
    		
			// 定义所需列数
//			int columnNum = rowConfig.size();
			HSSFRow rowRowName = sheet.createRow(2);				// 在索引2的位置创建行(最顶端的行开始的第二行)
			
			// 将列头设置到sheet的单元格中
			for(int n=0;n<rowConfig.size();n++){
				HSSFCell  cellRowName = rowRowName.createCell(n);				//创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);				//设置列头单元格的数据类型
    			HSSFRichTextString text = new HSSFRichTextString(rowConfig.get(n).getRowName());
    			cellRowName.setCellValue(text);									//设置列头单元格的值
    			cellRowName.setCellStyle(columnTopStyle);						//设置列头单元格样式
    		}
			
    		//将查询出的数据设置到sheet对应的单元格中
			for(int i=0;i<domainList.size();i++){
				
				Object obj = domainList.get(i);//遍历每个对象
				HSSFRow row = sheet.createRow(i+3);//创建所需的行数
				
				for(int j=0; j<rowConfig.size(); j++){
					HSSFCell  cell = null; 
					ExportExcelDomain rowDomain = rowConfig.get(j);
					
					//获取字段数据
					Object rowValueObj = ReflectDataUtil.getFieldValue(rowDomain.getRowCode(), obj);
					
					//设置单元格的数据类型
					switch(rowDomain.getRowType()){
					case HSSFCell.CELL_TYPE_NUMERIC:
							cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
							if(rowValueObj != null && !"".equals(rowValueObj)){
								cell.setCellValue(LongUtil.getLongValue(rowValueObj.toString(), null));						//设置单元格的值
							}	
							
							break;
					case HSSFCell.CELL_TYPE_STRING:
							cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
							
							if(rowValueObj != null && !"".equals(rowValueObj)){
								cell.setCellValue(rowValueObj.toString());						//设置单元格的值
							}else{
								cell.setCellValue("");
							}
							break;
					case 6:
						cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
						SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
						if(rowValueObj != null && !"".equals(rowValueObj)){
							
							String val = format.format((Date)rowValueObj);
							cell.setCellValue(val);						//设置单元格的值
							
						}else{
							cell.setCellValue("");
						}
						
						
						break;
					default:
						break;
					}
					cell.setCellStyle(style);									//设置单元格样式
				}
			}
			
			if(foot!=null && foot.trim().length()>0){
				// 产生表格标题行
		        HSSFRow rowf = sheet.createRow(domainList.size()+4);
		        HSSFCell cellFoot = rowf.createCell(0);
				sheet.addMergedRegion(new CellRangeAddress(domainList.size()+4, domainList.size()+5, 0, (rowConfig.size()-1)));  
				cellFoot.setCellStyle(columnTopStyle);
				cellFoot.setCellValue(foot);
			}
//			//让列宽随着导出的列长自动适应
//			for (int colNum = 0; colNum < rowConfig.size(); colNum++) {
//	            int columnWidth = sheet.getColumnWidth(colNum) / 256;
//	            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
//	                HSSFRow currentRow;
//	                //当前行未被使用过
//	                if (sheet.getRow(rowNum) == null) {
//	                    currentRow = sheet.createRow(rowNum);
//	                } else {
//	                    currentRow = sheet.getRow(rowNum);
//	                }
//	                if (currentRow.getCell(colNum) != null) {
//	                    HSSFCell currentCell = currentRow.getCell(colNum);
//	                    if (currentCell!=null && (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING)) {
//	                    	String cellString = currentCell.getStringCellValue();
//	                    	System.out.println("cellString:" + cellString);
//	                    	if(cellString!=null){
//		                        int length = currentCell.getStringCellValue().getBytes().length;
//		                        if (columnWidth < length) {
//		                            columnWidth = length;
//		                        }
//	                    	}
//	                    }
//	                }
//	            }
//	            if(colNum == 0){
//	            	sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
//	            }else{
//	            	sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
//	            }
//	        }
			
			if(workbook !=null){
				try
			    {
			        String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
			        String headStr = "attachment; filename=\"" + fileName + "\"";
			        
//			        File file = new File("d:/export/"+fileName);
//			        
//			        FileOutputStream fos = new FileOutputStream(file);
//			        workbook.write(fos);
//			        fos.flush();
//			        fos.close();
			        response.setContentType("APPLICATION/OCTET-STREAM");
			        response.setHeader("Content-Disposition", headStr);
			        OutputStream out = response.getOutputStream();
			        workbook.write(out);
			        out.flush();
			        out.close();
			    }
			    catch (IOException e)
			    {
			        e.printStackTrace();
			    }
			}

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}
	
	/*
	 * 导出数据
	 * */
	public void export(HttpServletResponse response) throws Exception{
		try{
			HSSFWorkbook workbook = new HSSFWorkbook();						// 创建工作簿对象
			HSSFSheet sheet = workbook.createSheet(title);		 			// 创建工作表
			
			// 产生表格标题行
	        HSSFRow rowm = sheet.createRow(0);
	        HSSFCell cellTiltle = rowm.createCell(0);
	        
	        //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
	        HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
	        HSSFCellStyle style = this.getStyle(workbook);					//单元格样式对象
	        
	        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));  
	        cellTiltle.setCellStyle(columnTopStyle);
	        cellTiltle.setCellValue(title);
    		
			// 定义所需列数
			int columnNum = rowName.length;
			HSSFRow rowRowName = sheet.createRow(2);				// 在索引2的位置创建行(最顶端的行开始的第二行)
			
			// 将列头设置到sheet的单元格中
			for(int n=0;n<columnNum;n++){
				HSSFCell  cellRowName = rowRowName.createCell(n);				//创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);				//设置列头单元格的数据类型
    			HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
    			cellRowName.setCellValue(text);									//设置列头单元格的值
    			cellRowName.setCellStyle(columnTopStyle);						//设置列头单元格样式
    		}
			
    		//将查询出的数据设置到sheet对应的单元格中
			for(int i=0;i<dataList.size();i++){
				
				Object[] obj = dataList.get(i);//遍历每个对象
				HSSFRow row = sheet.createRow(i+3);//创建所需的行数
				
				for(int j=0; j<obj.length; j++){
					HSSFCell  cell = null;   //设置单元格的数据类型
					if(j == 0){
						cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellValue(i+1);	
					}else{
						cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
						if(!"".equals(obj[j]) && obj[j] != null){
							cell.setCellValue(obj[j].toString());						//设置单元格的值
						}
					}
					cell.setCellStyle(style);									//设置单元格样式
				}
			}
			//让列宽随着导出的列长自动适应
			for (int colNum = 0; colNum < columnNum; colNum++) {
	            int columnWidth = sheet.getColumnWidth(colNum) / 256;
	            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
	                HSSFRow currentRow;
	                //当前行未被使用过
	                if (sheet.getRow(rowNum) == null) {
	                    currentRow = sheet.createRow(rowNum);
	                } else {
	                    currentRow = sheet.getRow(rowNum);
	                }
	                if (currentRow.getCell(colNum) != null) {
	                    HSSFCell currentCell = currentRow.getCell(colNum);
	                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
	                        int length = currentCell.getStringCellValue().getBytes().length;
	                        if (columnWidth < length) {
	                            columnWidth = length;
	                        }
	                    }
	                }
	            }
	            if(colNum == 0){
	            	sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
	            }else{
	            	sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
	            }
	        }
			
			if(workbook !=null){
				try
			    {
			        String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
			        String headStr = "attachment; filename=\"" + fileName + "\"";
			       
			        response.setContentType("APPLICATION/OCTET-STREAM");
			        response.setHeader("Content-Disposition", headStr);
			        OutputStream out = response.getOutputStream();
			        workbook.write(out);
			    }
			    catch (IOException e)
			    {
			        e.printStackTrace();
			    }
			}

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}
	
	/* 
	 * 列头单元格样式
	 */    
  	public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
  		
  		  // 设置字体
    	  HSSFFont font = workbook.createFont();
    	  //设置字体大小
    	  font.setFontHeightInPoints((short)11);
    	  //字体加粗
    	  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    	  //设置字体名字 
    	  font.setFontName("Courier New");
    	  //设置样式; 
    	  HSSFCellStyle style = workbook.createCellStyle();
    	  //设置底边框; 
    	  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    	  //设置底边框颜色;  
    	  style.setBottomBorderColor(HSSFColor.BLACK.index);
    	  //设置左边框;   
    	  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	  //设置左边框颜色; 
    	  style.setLeftBorderColor(HSSFColor.BLACK.index);
    	  //设置右边框; 
    	  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    	  //设置右边框颜色; 
    	  style.setRightBorderColor(HSSFColor.BLACK.index);
    	  //设置顶边框; 
    	  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	  //设置顶边框颜色;  
    	  style.setTopBorderColor(HSSFColor.BLACK.index);
    	  //在样式用应用设置的字体;  
    	  style.setFont(font);
    	  //设置自动换行; 
    	  style.setWrapText(false);
    	  //设置水平对齐的样式为居中对齐;  
    	  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	  //设置垂直对齐的样式为居中对齐; 
    	  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	  
    	  return style;
    	  
  	}
  	
  	/*  
	 * 列数据信息单元格样式
	 */  
  	public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
	  	  // 设置字体
	  	  HSSFFont font = workbook.createFont();
	  	  //设置字体大小
	  	  //font.setFontHeightInPoints((short)10);
	  	  //字体加粗
	  	  //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	  	  //设置字体名字 
	  	  font.setFontName("Courier New");
	  	  //设置样式; 
	  	  HSSFCellStyle style = workbook.createCellStyle();
	  	  //设置底边框; 
	  	  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	  	  //设置底边框颜色;  
	  	  style.setBottomBorderColor(HSSFColor.BLACK.index);
	  	  //设置左边框;   
	  	  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	  	  //设置左边框颜色; 
	  	  style.setLeftBorderColor(HSSFColor.BLACK.index);
	  	  //设置右边框; 
	  	  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	  	  //设置右边框颜色; 
	  	  style.setRightBorderColor(HSSFColor.BLACK.index);
	  	  //设置顶边框; 
	  	  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	  	  //设置顶边框颜色;  
	  	  style.setTopBorderColor(HSSFColor.BLACK.index);
	  	  //在样式用应用设置的字体;  
	  	  style.setFont(font);
	  	  //设置自动换行; 
	  	  style.setWrapText(false);
	  	  //设置水平对齐的样式为居中对齐;  
	  	  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	  	  //设置垂直对齐的样式为居中对齐; 
	  	  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	  	 
	  	  return style;
  	
  	}
}